<!DOCTYPE html><html><head><title>MySQL学习笔记（Day011：SELECT）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day011select">MySQL学习笔记（Day011：SELECT）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day011select">MySQL学习笔记（Day011：SELECT）</a><ul>
<li><a href="#一-select语法介绍">一. SELECT语法介绍</a></li>
<li><a href="#二-limit-和-order-by">二. LIMIT 和 ORDER BY</a></li>
<li><a href="#三-where">三. WHERE</a></li>
<li><a href="#四-join">四. JOIN</a><ul>
<li><a href="#41-inner-join">4.1. INNER JOIN</a></li>
<li><a href="#42-outer-join">4.2. OUTER JOIN</a></li>
<li><a href="#43-group-by">4.3. GROUP BY</a></li>
</ul>
</li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-11879" class="wmd-preview-section preview-content">

<h2 id="一-select语法介绍">一. SELECT语法介绍</h2>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/select.html" target="_blank">SELECT语法官方文档</a></p>
</blockquote></div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-operator"><span class="hljs-keyword">SELECT</span><br><span class="hljs-comment">-- -------------------------不推荐使用--------------------------</span><br>    [<span class="hljs-keyword">ALL</span> | <span class="hljs-keyword">DISTINCT</span> | <span class="hljs-keyword">DISTINCTROW</span> ]<br>      [<span class="hljs-keyword">HIGH_PRIORITY</span>]<br>      [MAX_STATEMENT_TIME = N]<br>      [<span class="hljs-keyword">STRAIGHT_JOIN</span>]<br>      [<span class="hljs-keyword">SQL_SMALL_RESULT</span>] [<span class="hljs-keyword">SQL_BIG_RESULT</span>] [<span class="hljs-keyword">SQL_BUFFER_RESULT</span>]<br>      [<span class="hljs-keyword">SQL_CACHE</span> | <span class="hljs-keyword">SQL_NO_CACHE</span>] [<span class="hljs-keyword">SQL_CALC_FOUND_ROWS</span>]<br><span class="hljs-comment">-- -------------------------------------------------------------</span><br>    select_expr [, select_expr ...]<br>    [<span class="hljs-keyword">FROM</span> table_references  <br>      [<span class="hljs-keyword">PARTITION</span> partition_list]<br>    [<span class="hljs-keyword">WHERE</span> where_condition] <br>    [<span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> {col_name | expr | <span class="hljs-keyword">position</span>}  <br>      [<span class="hljs-keyword">ASC</span> | <span class="hljs-keyword">DESC</span>], ... [<span class="hljs-keyword">WITH</span> <span class="hljs-keyword">ROLLUP</span>]] <br>    [<span class="hljs-keyword">HAVING</span> where_condition] <br>    [<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> {col_name | expr | <span class="hljs-keyword">position</span>} <br>      [<span class="hljs-keyword">ASC</span> | <span class="hljs-keyword">DESC</span>], ...]<br>    [<span class="hljs-keyword">LIMIT</span> {[offset,] <span class="hljs-keyword">row_count</span> | <span class="hljs-keyword">row_count</span> OFFSET offset}]  <br>    [<span class="hljs-keyword">PROCEDURE</span> procedure_name(argument_list)]<br>    [<span class="hljs-keyword">INTO</span> <span class="hljs-keyword">OUTFILE</span> <span class="hljs-string">'file_name'</span><br>        [<span class="hljs-built_in">CHARACTER</span> <span class="hljs-keyword">SET</span> charset_name]<br>        export_options<br>      | <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">DUMPFILE</span> <span class="hljs-string">'file_name'</span><br>      | <span class="hljs-keyword">INTO</span> var_name [, var_name]]<br>    [<span class="hljs-keyword">FOR</span> <span class="hljs-keyword">UPDATE</span> | <span class="hljs-keyword">LOCK</span> <span class="hljs-keyword">IN</span> <span class="hljs-keyword">SHARE</span> <span class="hljs-keyword">MODE</span>]]</span><br></code></pre>

</div><div id="wmd-preview-section-11920" class="wmd-preview-section preview-content">

<h2 id="二-limit-和-order-by">二. LIMIT 和 ORDER BY</h2></div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 从employees中 随机 取出一条数据，结果是不确定的</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- order by col_name 根据某列的值进行排序</span><br><span class="hljs-comment">-- asc ： 升序(default)</span><br><span class="hljs-comment">-- desc： 降序</span><br><span class="hljs-comment">--</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">asc</span>  <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span>   <span class="hljs-comment">-- 使用order by col_name asc进行升序排序</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span>  emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span>       <span class="hljs-comment">-- 默认就是升序的</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  <span class="hljs-comment">-- 结果和上面一致</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">desc</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">1</span>;</span>    <span class="hljs-comment">-- desc表示降序</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |  <span class="hljs-comment">-- 降序显示</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br><span class="hljs-comment">-- 通过order by排序后 limit 1 才是确定的</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> employees\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>       <span class="hljs-keyword">Table</span>: employees<br><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`employees`</span> (<br>  <span class="hljs-string">`emp_no`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`birth_date`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`first_name`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">14</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`last_name`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">16</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`gender`</span> enum(<span class="hljs-string">'M'</span>,<span class="hljs-string">'F'</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`hire_date`</span> <span class="hljs-built_in">date</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`emp_no`</span>)    <span class="hljs-comment">-- emp_no 是主键，order by 主键 不会创建临时表的，主键(索引)本身有序</span><br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">asc</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>,<span class="hljs-number">5</span>;</span>  <span class="hljs-comment">-- limit start, offset</span><br>                                                               <span class="hljs-comment">-- 从第5条 开始取，取5条出来</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |<br>|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |<br>|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |<br>|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |<br>|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 以上这个语法有一种分页的效果，但是会随着start的增加，性能会下降，因为会扫描表(从 1 到 start)</span><br><br><span class="hljs-comment">-- 相对比较推荐的方法</span><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">where</span> emp_no &gt; <span class="hljs-number">20000</span> <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  20001 | 1962-05-16 | Atreye     | Eppinger  | M      | 1990-04-18 |<br>|  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |<br>|  20003 | 1953-04-11 | Munehiko   | Coors     | F      | 1991-02-07 |<br>|  20004 | 1952-03-07 | Radoslaw   | Pfau      | M      | 1995-11-24 |<br>|  20005 | 1956-02-20 | Licheng    | Przulj    | M      | 1992-07-17 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment">-- （当然推荐把热数据放cache里,比如Redis）</span></span><br></code></pre>

<blockquote>
  <p><code>ORDER BY</code> 是把已经查询好的结果集进行排序</p>
</blockquote>

</div><div id="wmd-preview-section-11948" class="wmd-preview-section preview-content">

<h2 id="三-where">三. WHERE</h2>

<p><code>WHERE</code>是将查询出来的结果，通过<code>WHERE</code>后面的条件(condition)，对结果进行过滤</p></div><div id="wmd-preview-section-432" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees  <span class="hljs-keyword">where</span> emp_no &gt; <span class="hljs-number">30000</span>  emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">4</span>;</span> <span class="hljs-comment">-- 不加order by的limit是不确定的SQL</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  30001 | 1953-03-27 | Izaskun    | Morton    | M      | 1988-05-21 |<br>|  30002 | 1960-08-23 | Branimir   | Snedden   | M      | 1998-09-24 |<br>|  30003 | 1952-11-25 | Takahito   | Vilarrasa | M      | 1990-08-22 |<br>|  30004 | 1957-11-26 | Lucian     | Penttonen | F      | 1992-10-08 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees  <span class="hljs-keyword">where</span> emp_no &gt; <span class="hljs-number">40000</span>  <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">4</span>;</span> <br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>|  40001 | 1956-03-28 | Akemi      | Maliniak  | F      | 1987-08-06 |<br>|  40002 | 1960-03-15 | Nakhoon    | Badr      | M      | 1990-02-13 |<br>|  40003 | 1960-01-26 | Jacopo     | Marshall  | F      | 1991-09-30 |<br>|  40004 | 1955-09-09 | Anneke     | Stiles    | F      | 1986-03-05 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.02</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees  <br>    -&gt; <span class="hljs-keyword">where</span> emp_no &gt; <span class="hljs-number">40000</span><br>    -&gt; <span class="hljs-keyword">and</span> hire_date &gt; <span class="hljs-string">'1991-01-01'</span>   <span class="hljs-comment">-- 可以用 and 进行 逻辑与 </span><br>    -&gt; <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">4</span>;</span><br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name  | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>|  40003 | 1960-01-26 | Jacopo     | Marshall   | F      | 1991-09-30 |<br>|  40005 | 1961-02-27 | Zsolt      | Fairtlough | F      | 1991-07-08 |<br>|  40012 | 1955-02-07 | Chinhyun   | Ozeri      | F      | 1995-08-12 |<br>|  40015 | 1964-10-08 | Ioana      | Lemarechal | M      | 1997-08-07 |<br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>4 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees<br>    -&gt; <span class="hljs-keyword">where</span> (emp_no &gt; <span class="hljs-number">40000</span> <span class="hljs-keyword">and</span> birth_date &gt; <span class="hljs-string">'1961-01-01'</span>) <span class="hljs-comment">-- 使用()明确条件的逻辑规则</span><br>    -&gt;    <span class="hljs-keyword">or</span> (emp_no &gt; <span class="hljs-number">40000</span> <span class="hljs-keyword">and</span> hire_date &gt; <span class="hljs-string">'1991-01-01'</span>)  <span class="hljs-comment">-- 可以使用 or 做 逻辑或</span><br>    -&gt; <span class="hljs-keyword">order</span> <span class="hljs-keyword">by</span> emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>| emp_no | birth_date | first_name | last_name  | gender | hire_date  |<br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>|  40003 | 1960-01-26 | Jacopo     | Marshall   | F      | 1991-09-30 |<br>|  40005 | 1961-02-27 | Zsolt      | Fairtlough | F      | 1991-07-08 |<br>|  40006 | 1962-11-07 | Basim      | Panienski  | F      | 1986-12-27 |<br>|  40012 | 1955-02-07 | Chinhyun   | Ozeri      | F      | 1995-08-12 |<br>|  40015 | 1964-10-08 | Ioana      | Lemarechal | M      | 1997-08-07 |<br>+<span class="hljs-comment">--------+------------+------------+------------+--------+------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre></div><div id="wmd-preview-section-11989" class="wmd-preview-section preview-content">

<h2 id="四-join">四. JOIN</h2>

<p><img longdesc="./123.png" alt="Alt text" title="" type="image/png" src="" class=""></p></div><div id="wmd-preview-section-12153" class="wmd-preview-section preview-content">

<h3 id="41-inner-join">4.1. INNER JOIN</h3></div><div id="wmd-preview-section-3690" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- ANSI SQL 89</span><br><span class="hljs-comment">-- 关联employees表和titles表</span><br><span class="hljs-comment">-- 要求是 employees的emp_no 等于 titles的emp_no</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> employees,titles <span class="hljs-keyword">where</span> employees.emp_no = titles.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+</span><br>| emp_no | birth_date | first_name | last_name | gender | hire_date  | emp_no | title           | from_date  | to_date    |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+</span><br>|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |<br>|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |<br>|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |<br>|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  10004 | Engineer        | 1986-12-01 | 1995-12-01 |<br>|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |<br>+<span class="hljs-comment">--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 在上面的基础上只显示emp_no，名字，性别和职位名称</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> emp_no, <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name), gender, title <br>    -&gt; <span class="hljs-keyword">from</span> employees,titles<br>    -&gt; <span class="hljs-keyword">where</span> employees.emp_no = titles.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>ERROR 1052 (23000): Column 'emp_no' in field list is ambiguous  <span class="hljs-comment">-- 报错了，原因是emp_no两个表都有</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> employees.emp_no, <span class="hljs-comment">-- 指定了employees</span><br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name), gender, title<br>    -&gt; <span class="hljs-keyword">from</span> employees,titles<br>    -&gt; <span class="hljs-keyword">where</span> employees.emp_no = titles.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+-----------------------------------+--------+-----------------+</span><br>| emp_no | concat(last_name,' ', first_name) | gender | title           |<br>+<span class="hljs-comment">--------+-----------------------------------+--------+-----------------+</span><br>|  10001 | Facello Georgi                    | M      | Senior Engineer |<br>|  10002 | Simmel Bezalel                    | F      | Staff           |<br>|  10003 | Bamford Parto                     | M      | Senior Engineer |<br>|  10004 | Koblick Chirstian                 | M      | Engineer        |<br>|  10004 | Koblick Chirstian                 | M      | Senior Engineer |<br>+<span class="hljs-comment">--------+-----------------------------------+--------+-----------------+    </span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> employees.emp_no,<br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, title  <span class="hljs-comment">-- 对名字的列取一个别名叫emp_name</span><br>    -&gt; <span class="hljs-keyword">from</span> employees,titles<br>    -&gt; <span class="hljs-keyword">where</span> employees.emp_no = titles.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>| emp_no | emp_name          | gender | title           |  <span class="hljs-comment">-- 这里就显示了emp_name</span><br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>|  10001 | Facello Georgi    | M      | Senior Engineer |<br>|  10002 | Simmel Bezalel    | F      | Staff           |<br>|  10003 | Bamford Parto     | M      | Senior Engineer |<br>|  10004 | Koblick Chirstian | M      | Engineer        |<br>|  10004 | Koblick Chirstian | M      | Senior Engineer |<br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> e.emp_no,  <span class="hljs-comment">-- 使用表的别名</span><br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, title<br>    -&gt; <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">as</span> e,titles <span class="hljs-keyword">as</span> t      <span class="hljs-comment">-- 对表做别名</span><br>    -&gt; <span class="hljs-keyword">where</span> e.emp_no = t.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span>   <span class="hljs-comment">-- 使用报表的别名</span><br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>| emp_no | emp_name          | gender | title           |<br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>|  10001 | Facello Georgi    | M      | Senior Engineer |<br>|  10002 | Simmel Bezalel    | F      | Staff           |<br>|  10003 | Bamford Parto     | M      | Senior Engineer |<br>|  10004 | Koblick Chirstian | M      | Engineer        |<br>|  10004 | Koblick Chirstian | M      | Senior Engineer |<br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- ANSI SQL 92</span><br><span class="hljs-comment">-- inner join ... on ...语法</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> e.emp_no,<br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, title<br>    -&gt; <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">as</span> e <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> titles <span class="hljs-keyword">as</span> t  <span class="hljs-comment">-- inner join 可以省略inner关键字</span><br>    -&gt; <span class="hljs-keyword">on</span> e.emp_no = t.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span>             <span class="hljs-comment">-- 配合join使用on</span><br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>| emp_no | emp_name          | gender | title           |<br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>|  10001 | Facello Georgi    | M      | Senior Engineer |<br>|  10002 | Simmel Bezalel    | F      | Staff           |<br>|  10003 | Bamford Parto     | M      | Senior Engineer |<br>|  10004 | Koblick Chirstian | M      | Engineer        |<br>|  10004 | Koblick Chirstian | M      | Senior Engineer |<br>+<span class="hljs-comment">--------+-------------------+--------+-----------------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 上面两种语句在效率上其实是一样的，只是语法上的区别</span><br><span class="hljs-comment">--</span><br><span class="hljs-comment">--- 第一种</span><br>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> e.emp_no,<br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, title<br>    -&gt; <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">as</span> e,titles <span class="hljs-keyword">as</span> t<br>    -&gt; <span class="hljs-keyword">where</span> e.emp_no = t.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |<br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL               | 298124 |   100.00 | NULL        |<br>|  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using index |<br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> warning (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--- 第二种</span><br>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> e.emp_no,<br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, title<br>    -&gt; <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">as</span> e <span class="hljs-keyword">inner</span> <span class="hljs-keyword">join</span> titles <span class="hljs-keyword">as</span> t<br>    -&gt; <span class="hljs-keyword">on</span> e.emp_no = t.emp_no <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |<br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>|  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL               | 298124 |   100.00 | NULL        |<br>|  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using index |<br>+<span class="hljs-comment">----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> warning (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 通过explain看两条语句的执行计划，发现是一样的，所以性能上是一样的，只是语法的不同</span></span><br></code></pre>

</div><div id="wmd-preview-section-12169" class="wmd-preview-section preview-content">

<h3 id="42-outer-join">4.2. OUTER JOIN</h3></div><div id="wmd-preview-section-15227" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 左连接 left join</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> burn_test<br>Reading <span class="hljs-keyword">table</span> information <span class="hljs-keyword">for</span> completion <span class="hljs-keyword">of</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">and</span> <span class="hljs-keyword">column</span> <span class="hljs-keyword">names</span><br>You can turn off this feature <span class="hljs-keyword">to</span> <span class="hljs-keyword">get</span> a quicker startup <span class="hljs-keyword">with</span> -A<br><br><span class="hljs-keyword">Database</span> <span class="hljs-keyword">changed</span><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_left_join_1(a <span class="hljs-built_in">int</span>);</span><br>Query OK, 0 rows affected (0.16 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_left_join_2(b <span class="hljs-built_in">int</span>);</span> <br>Query OK, 0 rows affected (0.14 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_left_join_1 <span class="hljs-keyword">values</span> (<span class="hljs-number">1</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_left_join_1 <span class="hljs-keyword">values</span> (<span class="hljs-number">2</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_left_join_2 <span class="hljs-keyword">values</span> (<span class="hljs-number">1</span>);</span> <br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_left_join_1;</span><br>+<span class="hljs-comment">------+</span><br>| a    |<br>+<span class="hljs-comment">------+</span><br>|    1 |<br>|    2 |<br>+<span class="hljs-comment">------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_left_join_2;</span><br>+<span class="hljs-comment">------+</span><br>| b    |<br>+<span class="hljs-comment">------+</span><br>|    1 |<br>+<span class="hljs-comment">------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1 <br>    -&gt; <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span>   <span class="hljs-comment">-- 使用left join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2 <br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    1 |    1 |  <span class="hljs-comment">-- 满足条件的，显示t2中该条记录的值</span><br>|    2 | NULL |  <span class="hljs-comment">-- 不满足条件的，用NULL填充</span><br>+<span class="hljs-comment">------+------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment">-- left join ： 左表 left join 右表 on 条件；</span><br><span class="hljs-comment">--              左表全部显示，右表是匹配表，</span><br><span class="hljs-comment">--              如果右表的某条记录满足 [on 条件] 匹配，则该记录显示</span><br><span class="hljs-comment">--              如果右表的某条记录 不 满足 匹配，则该记录显示NULL</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 右连接 right join （继续使用test_left_join_1和2两张表）</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span><br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1<br>    -&gt; <span class="hljs-keyword">right</span> <span class="hljs-keyword">join</span>   <span class="hljs-comment">-- 使用right join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2<br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    1 |    1 |   <span class="hljs-comment">-- 右表（t2）全部显示</span><br>+<span class="hljs-comment">------+------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment">-- right join ： 左表 right join 右表 on 条件</span><br><span class="hljs-comment">--               右表全部显示，左边是匹配表</span><br><span class="hljs-comment">--               同样和left join，满足则显示，不满足且右表中有值，则填充NULL</span><br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_left_join_2 <span class="hljs-keyword">values</span> (<span class="hljs-number">3</span>);</span>  <span class="hljs-comment">-- t2 中再增加一条记录</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span><br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1<br>    -&gt; <span class="hljs-keyword">right</span> <span class="hljs-keyword">join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2<br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    1 |    1 |<br>| NULL |    3 | <span class="hljs-comment">-- 右表存在，左表没有，用NULL填充</span><br>+<span class="hljs-comment">------+------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 查找在t1表，而不在t2表的数据</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1 <br>    -&gt; <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2 <br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b <span class="hljs-keyword">where</span> t2.b <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    2 | NULL |  <span class="hljs-comment">-- 数据1 在t1和t2中都有，所以不显示</span><br>+<span class="hljs-comment">------+------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">-- left join ： left outer join , outer关键字可以省略</span><br><span class="hljs-comment">-- right join： right outer join , outer 关键字可以省略</span><br><br><span class="hljs-comment">-- join无论inner还是outer，列名不需要一样，甚至列的类型也可以不一样，会进行转换。</span><br><span class="hljs-comment">-- 一般情况下，表设计合理，需要关联的字段类型应该是一样的</span><br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 查找哪些员工不是经理</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> e.emp_no,<br>    -&gt; <span class="hljs-keyword">concat</span>(last_name,<span class="hljs-string">' '</span>, first_name) <span class="hljs-keyword">as</span> emp_name, gender, d.dept_no<br>    -&gt; <span class="hljs-keyword">from</span> employees <span class="hljs-keyword">as</span> e <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> dept_manager <span class="hljs-keyword">as</span> d<br>    -&gt; <span class="hljs-keyword">on</span> e.emp_no = d.emp_no<br>    -&gt; <span class="hljs-keyword">where</span> d.emp_no <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span> <span class="hljs-keyword">limit</span> <span class="hljs-number">5</span>;</span><br>+<span class="hljs-comment">--------+-------------------+--------+---------+</span><br>| emp_no | emp_name          | gender | dept_no | <span class="hljs-comment">-- dept_no是dept_manager的字段</span><br>+<span class="hljs-comment">--------+-------------------+--------+---------+</span><br>|  10001 | Facello Georgi    | M      | NULL    |<br>|  10002 | Simmel Bezalel    | F      | NULL    |<br>|  10003 | Bamford Parto     | M      | NULL    |<br>|  10004 | Koblick Chirstian | M      | NULL    |<br>|  10005 | Maliniak Kyoichi  | M      | NULL    |<br>+<span class="hljs-comment">--------+-------------------+--------+---------+</span><br>5 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- 在 inner join中，过滤条件放在where或者on中都是可以的</span><br><span class="hljs-comment">-- 在 outer join中 条件放在where和on中是不一样的</span><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1<br>    -&gt; <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2<br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b<br>    -&gt; <span class="hljs-keyword">where</span> t2.b <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>;</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    2 | NULL |<br>+<span class="hljs-comment">------+------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <br>    -&gt; test_left_join_1 <span class="hljs-keyword">as</span> t1<br>    -&gt; <span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span><br>    -&gt; test_left_join_2 <span class="hljs-keyword">as</span> t2<br>    -&gt; <span class="hljs-keyword">on</span> t1.a = t2.b<br>    -&gt; <span class="hljs-keyword">and</span> t2.b <span class="hljs-keyword">is</span> <span class="hljs-literal">null</span>;</span>  <span class="hljs-comment">-- 除了a=b, 还要找到b=null的，但是b里面没有null，所有a全部显示，b全为null</span><br>+<span class="hljs-comment">------+------+</span><br>| a    | b    |<br>+<span class="hljs-comment">------+------+</span><br>|    1 | NULL |<br>|    2 | NULL |<br>+<span class="hljs-comment">------+------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">-- ON 参与outer join的结果的生成，而where只是对结果的一个过滤</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 作业：查处普通员工的title，部门名称，薪资</span><br><span class="hljs-comment">--</span><br><br><br></span><br></code></pre></div><div id="wmd-preview-section-12213" class="wmd-preview-section preview-content">

<h3 id="43-group-by">4.3. GROUP BY</h3>

</div><div id="wmd-preview-section-15214" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 找出同一个部门的员工数量</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> dept_no, <span class="hljs-keyword">count</span>(dept_no)  <span class="hljs-comment">-- count是得到数量，这里就是分组函数</span><br>    -&gt; <span class="hljs-keyword">from</span> dept_emp<br>    -&gt; <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> dept_no;</span>  <span class="hljs-comment">-- 通过 dept_no 分组</span><br>+<span class="hljs-comment">---------+----------------+</span><br>| dept_no | count(dept_no) |<br>+<span class="hljs-comment">---------+----------------+</span><br>| d001    |          20211 |<br>| d002    |          17346 |<br>| d003    |          17786 |<br>| d004    |          73485 |<br>| d005    |          85707 |<br>| d006    |          20117 |<br>| d007    |          52245 |<br>| d008    |          21126 |<br>| d009    |          23580 |<br>+<span class="hljs-comment">---------+----------------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.10</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 选出部门人数 &gt; 50000 </span><br><span class="hljs-comment">-- </span><br>mysql&gt; <span class="hljs-keyword">select</span> dept_no, <span class="hljs-keyword">count</span>(dept_no)<br>    -&gt; <span class="hljs-keyword">from</span> dept_emp<br>    -&gt; <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> dept_no<br>    -&gt; <span class="hljs-keyword">having</span> <span class="hljs-keyword">count</span>(dept_no) &gt; <span class="hljs-number">50000</span>;</span>  <span class="hljs-comment">-- 如果是对分组的聚合函数做过滤，使用having，用where报语法错误</span><br>+<span class="hljs-comment">---------+----------------+</span><br>| dept_no | count(dept_no) |<br>+<span class="hljs-comment">---------+----------------+</span><br>| d004    |          73485 |<br>| d005    |          85707 |<br>| d007    |          52245 |<br>+<span class="hljs-comment">---------+----------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.09</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 每个用户每个月产生的订单数目</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">desc</span> orders;</span><br>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br>| Field           | Type        | Null | Key | Default | Extra |<br>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br>| o_orderkey      | int(11)     | NO   | PRI | NULL    |       |  <span class="hljs-comment">-- 订单ID</span><br>| o_custkey       | int(11)     | YES  | MUL | NULL    |       |  <span class="hljs-comment">-- 客户ID</span><br>| o_orderstatus   | char(1)     | YES  |     | NULL    |       |<br>| o_totalprice    | double      | YES  |     | NULL    |       |<br>| o_orderDATE     | date        | YES  | MUL | NULL    |       |  <span class="hljs-comment">-- 订单日期</span><br>| o_orderpriority | char(15)    | YES  |     | NULL    |       |<br>| o_clerk         | char(15)    | YES  |     | NULL    |       |<br>| o_shippriority  | int(11)     | YES  |     | NULL    |       |<br>| o_comment       | varchar(79) | YES  |     | NULL    |       |<br>+<span class="hljs-comment">-----------------+-------------+------+-----+---------+-------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span>  o_orderkey, o_custkey, o_orderDATE <span class="hljs-keyword">from</span> orders <span class="hljs-keyword">limit</span> <span class="hljs-number">3</span>;</span> <br>+<span class="hljs-comment">------------+-----------+-------------+</span><br>| o_orderkey | o_custkey | o_orderDATE |<br>+<span class="hljs-comment">------------+-----------+-------------+</span><br>|          1 |     36901 | 1996-01-02  |<br>|          2 |     78002 | 1996-12-01  |<br>|          3 |    123314 | 1993-10-14  |<br>+<span class="hljs-comment">------------+-----------+-------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 查找客户每年每月产生的订单数</span><br><span class="hljs-comment">--</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> o_custkey, <span class="hljs-keyword">count</span>(o_orderkey),                                                                  -&gt; <span class="hljs-keyword">year</span>(o_orderDATE), <span class="hljs-keyword">month</span>(o_orderDATE)<br>    -&gt; <span class="hljs-keyword">from</span> orders<br>    -&gt; <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> o_custkey, <span class="hljs-keyword">year</span>(o_orderDATE), <span class="hljs-keyword">month</span>(o_orderDATE)<br>    -&gt; <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span><br>+<span class="hljs-comment">-----------+-------------------+-------------------+--------------------+</span><br>| o_custkey | count(o_orderkey) | year(o_orderDATE) | month(o_orderDATE) |<br>+<span class="hljs-comment">-----------+-------------------+-------------------+--------------------+</span><br>|         1 |                 1 |              1992 |                  4 |<br>|         1 |                 1 |              1992 |                  8 |<br>|         1 |                 1 |              1996 |                  6 |<br>|         1 |                 1 |              1996 |                  7 |<br>|         1 |                 1 |              1996 |                 12 |<br>|         1 |                 1 |              1997 |                  3 |<br>|         2 |                 1 |              1992 |                  4 |<br>|         2 |                 1 |              1994 |                  5 |<br>|         2 |                 1 |              1994 |                  8 |<br>|         2 |                 1 |              1994 |                 12 |<br>+<span class="hljs-comment">-----------+-------------------+-------------------+--------------------+</span><br>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">8.97</span> sec)<br><br><span class="hljs-comment">-- 使用 date_format 函数</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> o_custkey, <span class="hljs-keyword">count</span>(o_orderkey),<br>    -&gt; <span class="hljs-keyword">date_format</span>(o_orderDATE, <span class="hljs-string">'%Y-%m'</span>) <span class="hljs-keyword">as</span> <span class="hljs-built_in">date</span><br>    -&gt; <span class="hljs-keyword">from</span> orders<br>    -&gt; <span class="hljs-keyword">group</span> <span class="hljs-keyword">by</span> o_custkey, <span class="hljs-keyword">date_format</span>(o_orderDATE, <span class="hljs-string">'%Y-%m'</span>)<br>    -&gt; <span class="hljs-keyword">limit</span> <span class="hljs-number">10</span>;</span><br>+<span class="hljs-comment">-----------+-------------------+---------+</span><br>| o_custkey | count(o_orderkey) | date    |<br>+<span class="hljs-comment">-----------+-------------------+---------+</span><br>|         1 |                 1 | 1992-04 |<br>|         1 |                 1 | 1992-08 |<br>|         1 |                 1 | 1996-06 |<br>|         1 |                 1 | 1996-07 |<br>|         1 |                 1 | 1996-12 |<br>|         1 |                 1 | 1997-03 |<br>|         2 |                 1 | 1992-04 |<br>|         2 |                 1 | 1994-05 |<br>|         2 |                 1 | 1994-08 |<br>|         2 |                 1 | 1994-12 |<br>+<span class="hljs-comment">-----------+-------------------+---------+</span><br>10 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">11.46</span> sec)<br><br><br><span class="hljs-comment">-- 作业：查找客户每周（以年，月，周 显示）产生的订单量</span><br><br></span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>